package cn.zy.company.dao;

import cn.zy.company.entity.Work;
import cn.zy.company.util.DBUtil;
import cn.zy.company.util.DataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class WorkDao {
    public void add(Work work) throws Exception {
        Connection connection= DataSource.getConnection();
        DBUtil.insert(connection,work);
        DataSource.closeConnection(connection);
    }
    public void delete(Integer id) throws Exception {
        Connection connection = DataSource.getConnection();
        DBUtil.delete(connection,id,Work.class);
        DataSource.closeConnection(connection);
    }
    public void update(Work work) throws Exception {
        Connection connection = DataSource.getConnection();
        DBUtil.update(connection,work);
        DataSource.closeConnection(connection);
    }
    public Work findById(Integer id) throws Exception {
        Connection connection = DataSource.getConnection();
        Work work = DBUtil.selectOne(connection, id, Work.class);
        return work;
    }
    public List<Work> findAll() throws Exception {
        Connection connection = DataSource.getConnection();
        List<Work> list = DBUtil.select(connection, "", new Object[]{}, Work.class);
        DataSource.closeConnection(connection);
        return list;
    }
    public List<Work> findAll(Integer page,Integer size) throws Exception {
        List<Work> list=new ArrayList<Work>();
        Work work=null;
        Connection connection = DataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select w.*,m.name member_name from work w left join member m on  w.member=m.id limit ?,?");
        preparedStatement.setObject(1,(page-1)*size);
        preparedStatement.setObject(2,size);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            work=new Work(resultSet.getString("name"),resultSet.getNString("image"),resultSet.getNString("value"),resultSet.getDate("date"),resultSet.getInt("member"));
            work.setId(resultSet.getInt("id"));
            list.add(work);
        }
        resultSet.close();
        preparedStatement.close();
        DataSource.closeConnection(connection);
        return list;
    }
    public long countAll() throws SQLException {
        Connection connection = DataSource.getConnection();
        long count = DBUtil.count(connection, "", new Object[]{}, Work.class);
        return count;
    }
}
